package org.yl.db_core.utils.db;

import org.yl.db_core.model.YlUserTestModel;
import org.yl.db_core.utils.constant.SqlConstant;
import org.yl.db_core.utils.TypeToCast;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Description: 生成sql
 * @Author: YL
 * @CreateDate: 2018/11/10 14:39
 * @Version: 1.0
 */
@Component
public class CreateMysqlSqlUtil {

    private final static Logger logger = LoggerFactory.getLogger(CreateMysqlSqlUtil.class);

    private final static TypeToCast typeToCast = new TypeToCast();

    synchronized
    public Map createQuerySQL(Object object, String methodType, List list, Integer page, Integer row) {
        Map result = new HashMap();
        /**我们项目的所有实体类都继承BaseModel （所有实体基类：该类只是串行化一下） && object instanceof BaseModel*/
        try {
            if (object != null) {
                /**得到该类*/
                Class<?> clazz = object.getClass();
                /**获取实体类所有属性，返回Field数组*/
                Field[] fields = clazz.getDeclaredFields();
                /**开始拼接SQL*/
                StringBuffer sbSql = new StringBuffer();
                /**获取表名*/
                String tableName = typeToCast.toTableName(clazz.getSimpleName());
                if (SqlConstant.SELECT_LIST.equalsIgnoreCase(methodType) || SqlConstant.SELECT_LIST_COUNT.equalsIgnoreCase(methodType)) {
                    sbSql.append("SELECT * FROM ").append(tableName);
                } else if (SqlConstant.UPDATE.equalsIgnoreCase(methodType)) {
                    sbSql.append("UPDATE ").append(tableName).append(" SET ");
                } else if (SqlConstant.INSERT.equalsIgnoreCase(methodType)
                        || SqlConstant.BATCHINSERT.equalsIgnoreCase(methodType)) {
                    sbSql.append("INSERT INTO ").append(tableName).append(" ( ");
                } else if (SqlConstant.DELETE.equalsIgnoreCase(methodType)) {
                    sbSql.append("DELETE FROM ").append(tableName);
                } else if (SqlConstant.SELECT_ID.equalsIgnoreCase(methodType)) {
                    sbSql.append("SELECT * FROM ").append(tableName);
                }/*else if(SqlConstant.SELECT_LIST_COUNT.equalsIgnoreCase(methodType)){
                    sbSql.append("SELECT COUNT(*) FROM ").append(tableName);
                }*/
                /**定义一个有序的map*/
                LinkedHashMap linkedHashMap = new LinkedHashMap();
                String strSql = "";
                String strSql2 = "";
                /**得到实体数据*/
                Map map = typeToCast.convertBeanToMap(object);
                /**如果是批量操作*/
                List<Object[]> parameters = new ArrayList<Object[]>();
                if (SqlConstant.BATCHINSERT.equalsIgnoreCase(methodType)) {
                    for (Object parameter : list) {
                        for (Field field : fields) {
                            if (!"id".equalsIgnoreCase(field.getName())) {
                                strSql += field.getName() + ", ";
                                strSql2 += " ?, ";
                                if (StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                    linkedHashMap.put(field.getName(), toObjectType(String.valueOf(field.getGenericType())));
                                } else {
                                    linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                                }
                            }
                        }
                        if (SqlConstant.INSERT.equalsIgnoreCase(methodType)) {
                            strSql = strSql.substring(0, strSql.lastIndexOf(","));
                            strSql2 = strSql2.substring(0, strSql2.lastIndexOf(","));
                            sbSql.append(strSql + ") VALUES ( ").append(strSql2 + " )");
                        }
                        Object[] objects = typeToCast.mapToObjects(linkedHashMap);
                        parameters.add(objects);
                    }
                }else {
                    for (Field field : fields) {
                        /**所有的属性类型*/
                        if (SqlConstant.SELECT_LIST.equalsIgnoreCase(methodType) || SqlConstant.SELECT_LIST_COUNT.equalsIgnoreCase(methodType)) {
                            if (!StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                //if (!"long".equals(String.valueOf(field.getGenericType()))
                                //&& !"0".equals(String.valueOf(map.get(field.getName())))) {
                                //}
                                linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                                if (!"class java.lang.String".equals(String.valueOf(field.getGenericType()))){
                                    strSql2 += " " + field.getName() + " = ? AND";
                                }else {
                                    strSql2 += " " + field.getName() + " LIKE CONCAT('%',?,'%') AND";
                                }
                            }
                        }
                        if (SqlConstant.UPDATE.equalsIgnoreCase(methodType)) {
                            if (!"id".equalsIgnoreCase(field.getName())) {
                                strSql += field.getName() + " = ?, ";
                                if (StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                    linkedHashMap.put(field.getName(), toObjectType(String.valueOf(field.getGenericType())));
                                } else {
                                    linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                                }
                            }
                        }
                        if (SqlConstant.INSERT.equalsIgnoreCase(methodType)) {
                            if (!"id".equalsIgnoreCase(field.getName())) {
                                strSql += field.getName() + ", ";
                                strSql2 += " ?, ";
                                if (StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                    linkedHashMap.put(field.getName(), toObjectType(String.valueOf(field.getGenericType())));
                                } else {
                                    linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                                }
                            }
                        }
                        if (SqlConstant.DELETE.equalsIgnoreCase(methodType)) {
                            if (!StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                strSql2 += " " + field.getName() + " = ?  AND";
                                linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                            }
                        }
                        if (SqlConstant.SELECT_ID.equalsIgnoreCase(methodType)) {
                            if (!StringUtils.isEmpty(String.valueOf(map.get(field.getName())))) {
                                strSql2 += " " + field.getName() + " = ?  AND";
                                linkedHashMap.put(field.getName(), String.valueOf(map.get(field.getName())));
                            }
                        }

                    }
                    if (SqlConstant.SELECT_LIST.equalsIgnoreCase(methodType)  || SqlConstant.SELECT_LIST_COUNT.equalsIgnoreCase(methodType)) {
                        if (strSql2 != "" && strSql2 != null) {
                            strSql2 = strSql2.substring(0, strSql2.lastIndexOf("AND"));
                            sbSql.append(" WHERE ").append(strSql2);
                        }
                        // Mysql 分页
                        if(SqlConstant.SELECT_LIST_COUNT.equalsIgnoreCase(methodType)){
                        }else{
                            if (page != null){
                                page = row * (page - 1);
                            }
                            if (page != null && row!= null) {
                                sbSql.append("  LIMIT "+row+" OFFSET "+page);
                            }else{
                                sbSql.append("  LIMIT 10 OFFSET 1");
                            }
                        }

                    }
                    if (SqlConstant.UPDATE.equalsIgnoreCase(methodType)) {
                        strSql = strSql.substring(0, strSql.lastIndexOf(","));
                        sbSql.append(strSql + " WHERE id = ?");
                        linkedHashMap.put("id", String.valueOf(map.get("id")));
                    }
                    if (SqlConstant.INSERT.equalsIgnoreCase(methodType)) {
                        strSql = strSql.substring(0, strSql.lastIndexOf(","));
                        strSql2 = strSql2.substring(0, strSql2.lastIndexOf(","));
                        sbSql.append(strSql + ") VALUES ( ").append(strSql2 + " )");
                    }
                    if (SqlConstant.DELETE.equalsIgnoreCase(methodType)) {
                        if (strSql2 != "" && strSql2 != null) {
                            strSql2 = strSql2.substring(0, strSql2.lastIndexOf("AND"));
                            sbSql.append(" WHERE ").append(strSql2);
                        }
                    }
                    if (SqlConstant.SELECT_ID.equalsIgnoreCase(methodType)) {
                        if (strSql2 != "" && strSql2 != null) {
                            strSql2 = strSql2.substring(0, strSql2.lastIndexOf("AND"));
                            sbSql.append(" WHERE ").append(strSql2);
                        }
                    }
                }
                Object[] objects = typeToCast.mapToObjects(linkedHashMap);
                result.put("sql", sbSql);
                logger.info("生成sql: {}", sbSql);
                if (SqlConstant.BATCHINSERT.equalsIgnoreCase(methodType)) {
                    result.put("objects", parameters);
                    logger.info("生成List<Object[]>數據: {}", parameters);
                }else {
                    result.put("objects", objects);
                    logger.info("生成Object[]數據: {}", linkedHashMap);
                }
                return result;
            }
        } catch (Exception e) {
            logger.error("生成sql异常: {}", e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    private Object toObjectType(String type) {
        if ("class java.lang.String".equalsIgnoreCase(String.valueOf(type))) {
            return "";
        }
        if ("class java.lang.Integer".equalsIgnoreCase(String.valueOf(type))) {
            return 0;
        }
        if ("class java.lang.Double".equalsIgnoreCase(String.valueOf(type))) {
            return 0.0;
        }
        if ("class java.lang.Boolean".equalsIgnoreCase(String.valueOf(type))) {
            return true;
        }
        if ("class java.lang.Short".equalsIgnoreCase(String.valueOf(type))) {
            return 0;
        }
        if ("class java.util.Date".equalsIgnoreCase(String.valueOf(type))) {
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return df.format(new Date());
        }
        if ("class java.lang.long".equalsIgnoreCase(String.valueOf(type))) {
            return 0;
        }
        if ("int".equalsIgnoreCase(String.valueOf(type))) {
            return 0;
        }
        if ("long".equalsIgnoreCase(String.valueOf(type))) {
            return 0;
        }
        return null;
    }

    public static void main(String[] args) {
        CreateMysqlSqlUtil createMysqlSqlUtil = new CreateMysqlSqlUtil();
        createMysqlSqlUtil.createQuerySQL(new YlUserTestModel(), SqlConstant.DELETE, new ArrayList(), null, null);
    }
}
